package metadata

import (
	"github.com/pingcap/errors"
	"github.com/romberli/das/global"
	"github.com/romberli/das/internal/dependency/metadata"
	"github.com/romberli/go-util/constant"
	"github.com/romberli/go-util/middleware"
	"github.com/romberli/log"
)

// Cluster type code
const (
	ClusterTypeSingle = 1
	ClusterTypeShard  = 2
)

var _ metadata.MySQLClusterRepo = (*MySQLClusterRepo)(nil)

// MySQLClusterRepo implements dependency.MySQLClusterRepo interface
type MySQLClusterRepo struct {
	Database middleware.Pool
}

// NewMySQLClusterRepo returns metadata.MySQLClusterRepo with given middleware.Pool
func NewMySQLClusterRepo(db middleware.Pool) metadata.MySQLClusterRepo {
	return newMySQLClusterRepo(db)
}

// NewMySQLClusterRepoWithGlobal returns metadata.MySQLClusterRepo with global mysql pool
func NewMySQLClusterRepoWithGlobal() metadata.MySQLClusterRepo {
	return newMySQLClusterRepo(global.DASMySQLPool)
}

// newMySQLClusterRepo returns *MySQLClusterRepo with given middleware.Pool
func newMySQLClusterRepo(db middleware.Pool) *MySQLClusterRepo {
	return &MySQLClusterRepo{db}
}

// Execute implements metadata.MySQLClusterRepo interface,
// it executes command with arguments on database
func (mcr *MySQLClusterRepo) Execute(command string, args ...interface{}) (middleware.Result, error) {
	conn, err := mcr.Database.Get()
	if err != nil {
		return nil, err
	}
	defer func() {
		err = conn.Close()
		if err != nil {
			log.Errorf("metadata MySQLClusterRepo.Execute(): close database connection failed.\n%+v", err)
		}
	}()

	return conn.Execute(command, args...)
}

// Transaction returns middleware.PoolConn
func (mcr *MySQLClusterRepo) Transaction() (middleware.Transaction, error) {
	return mcr.Database.Transaction()
}

// GetAll returns all available entities
func (mcr *MySQLClusterRepo) GetAll() ([]metadata.MySQLCluster, error) {
	sql := `
		SELECT id, cluster_name, middleware_cluster_id, monitor_system_id, env_id, del_flag, create_time, last_update_time
		FROM t_meta_mysql_cluster_info
		WHERE del_flag = 0
		ORDER BY id;
	`
	log.Debugf("metadata MySQLClusterRepo.GetAll() sql: \n%s", sql)

	result, err := mcr.Execute(sql)
	if err != nil {
		return nil, err
	}
	// init []metadata.MySQLCluster
	mysqlClusterInfoList := make([]metadata.MySQLCluster, result.RowNumber())
	for i := range mysqlClusterInfoList {
		mysqlClusterInfoList[i] = NewEmptyMySQLClusterInfoWithGlobal()
	}
	// map to struct
	err = result.MapToStructSlice(mysqlClusterInfoList, constant.DefaultMiddlewareTag)
	if err != nil {
		return nil, err
	}
	// init []metadata.MySQLCluster
	mysqlClusterList := make([]metadata.MySQLCluster, result.RowNumber())
	for i := range mysqlClusterList {
		mysqlClusterList[i] = mysqlClusterInfoList[i]
	}

	return mysqlClusterList, nil
}

// GetByEnv gets mysql clusters of given env id from the middleware
func (mcr *MySQLClusterRepo) GetByEnv(envID int) ([]metadata.MySQLCluster, error) {
	sql := `
		SELECT id, cluster_name, middleware_cluster_id, monitor_system_id, env_id, del_flag, create_time, last_update_time
		FROM t_meta_mysql_cluster_info
		WHERE del_flag = 0
		  AND env_id = ? ;
	`
	log.Debugf("metadata MySQLClusterRepo.GetByEnv() sql: \n%s\nplaceholders: %d", sql, envID)

	result, err := mcr.Execute(sql, envID)
	if err != nil {
		return nil, err
	}

	mysqlClusterList := make([]metadata.MySQLCluster, result.RowNumber())
	for row := range mysqlClusterList {
		mysqlClusterList[row] = NewEmptyMySQLClusterInfoWithGlobal()
	}
	// map to struct
	err = result.MapToStructSlice(mysqlClusterList, constant.DefaultMiddlewareTag)
	if err != nil {
		return nil, err
	}

	return mysqlClusterList, nil
}

// GetByID Select returns an available entity of the given id
func (mcr *MySQLClusterRepo) GetByID(id int) (metadata.MySQLCluster, error) {
	sql := `
		SELECT id, cluster_name, middleware_cluster_id, monitor_system_id, env_id, del_flag, create_time, last_update_time
		FROM t_meta_mysql_cluster_info
		WHERE del_flag = 0
		  AND id = ? ;
	`
	log.Debugf("metadata MySQLClusterRepo.GetByID() sql: \n%s\nplaceholders: %d", sql, id)

	result, err := mcr.Execute(sql, id)
	if err != nil {
		return nil, err
	}
	switch result.RowNumber() {
	case 0:
		return nil, errors.Errorf("metadata MySQLClusterRepo.GetByID(): data does not exist, id: %d", id)
	case 1:
		mysqlClusterInfo := NewEmptyMySQLClusterInfoWithGlobal()
		// map to struct
		err = result.MapToStructByRowIndex(mysqlClusterInfo, constant.ZeroInt, constant.DefaultMiddlewareTag)
		if err != nil {
			return nil, err
		}

		return mysqlClusterInfo, nil
	default:
		return nil, errors.Errorf("metadata MySQLClusterRepo.GetByID(): duplicate key exists, id: %d", id)
	}
}

// GetByName gets a mysql cluster of given cluster name from the middle ware
func (mcr *MySQLClusterRepo) GetByName(clusterName string) (metadata.MySQLCluster, error) {
	sql := `
		SELECT id, cluster_name, middleware_cluster_id, monitor_system_id, env_id, del_flag, create_time, last_update_time
		FROM t_meta_mysql_cluster_info
		WHERE del_flag = 0
		  AND cluster_name = ? ;
	`
	log.Debugf("metadata MySQLClusterRepo.GetByName() select sql:\n%s\nplaceholders: %s", sql, clusterName)
	result, err := mcr.Execute(sql, clusterName)
	if err != nil {
		return nil, err
	}
	switch result.RowNumber() {
	case 0:
		return nil, errors.Errorf("metadata MySQLClusterRepo.GetByName(): data does not exist, clusterName: %s", clusterName)
	case 1:
		mysqlClusterInfo := NewEmptyMySQLClusterInfoWithGlobal()
		// map to struct
		err = result.MapToStructByRowIndex(mysqlClusterInfo, constant.ZeroInt, constant.DefaultMiddlewareTag)
		if err != nil {
			return nil, err
		}

		return mysqlClusterInfo, nil
	default:
		return nil, errors.Errorf("metadata MySQLClusterRepo.GetByName(): duplicate key exists, clusterName: %s", clusterName)
	}
}

// GetID checks identity of given entity from the middleware
func (mcr *MySQLClusterRepo) GetID(clusterName string) (int, error) {
	sql := `SELECT id FROM t_meta_mysql_cluster_info WHERE del_flag = 0 AND cluster_name = ?;`
	log.Debugf("metadata MySQLClusterRepo.GetID() select sql:\n%s\nplaceholders: %s", sql, clusterName)
	result, err := mcr.Execute(sql, clusterName)
	if err != nil {
		return constant.ZeroInt, err
	}

	return result.GetInt(constant.ZeroInt, constant.ZeroInt)
}

// GetDBsByID gets the databases of the given id from the middleware
func (mcr *MySQLClusterRepo) GetDBsByID(id int) ([]metadata.DB, error) {
	sql := `
		SELECT di.id, di.db_name, di.cluster_id, di.cluster_type, di.env_id, di.del_flag, di.create_time, di.last_update_time
		FROM t_meta_db_info di
			INNER JOIN t_meta_mysql_cluster_info mci ON di.cluster_id = mci.id AND di.cluster_type = 1
		WHERE di.del_flag = 0
		  AND mci.del_flag = 0
		  AND mci.id = ? ;
	`
	log.Debugf("metadata MySQLClusterRepo.GetDBsByID() sql: \n%s\nplaceholders: %d", sql, id)

	result, err := mcr.Execute(sql, id)
	if err != nil {
		return nil, err
	}

	dbList := make([]metadata.DB, result.RowNumber())
	for row := range dbList {
		dbList[row] = NewEmptyDBInfoWithGlobal()
	}
	// map to struct
	err = result.MapToStructSlice(dbList, constant.DefaultMiddlewareTag)
	if err != nil {
		return nil, err
	}

	return dbList, nil
}

// GetResourceGroupByID get the resource group of the given id from the middleware
func (mcr *MySQLClusterRepo) GetResourceGroupByID(id int) (metadata.ResourceGroup, error) {
	sql := `
		SELECT DISTINCT rgi.id,
						rgi.group_uuid,
						rgi.group_name,
						rgi.del_flag,
						rgi.create_time,
						rgi.last_update_time
		FROM t_meta_resource_group_info rgi
			INNER JOIN t_meta_mysql_cluster_resource_group_map mcrgm ON rgi.id = mcrgm.resource_group_id
			INNER JOIN t_meta_mysql_cluster_info mci ON mcrgm.mysql_cluster_id = mci.id
		WHERE rgi.del_flag = 0
		  AND mcrgm.del_flag = 0
		  AND mci.del_flag = 0
		  AND mci.id = ? ;
	`
	log.Debugf("metadata MySQLClusterRepo.GetResourceGroupsByID() sql: \n%s\nplaceholders: %d", sql, id)

	result, err := mcr.Execute(sql, id)
	if err != nil {
		return nil, err
	}

	switch result.RowNumber() {
	case 0:
		return nil, errors.Errorf("metadata MySQLClusterRepo.GetResourceGroupByID(): data does not exist, id: %d", id)
	case 1:
		resourceGroupInfo := NewEmptyResourceGroupInfoWithGlobal()
		// map to struct
		err = result.MapToStructByRowIndex(resourceGroupInfo, constant.ZeroInt, constant.DefaultMiddlewareTag)
		if err != nil {
			return nil, err
		}
		return resourceGroupInfo, nil
	default:
		return nil, errors.Errorf("metadata MySQLClusterRepo.GetResourceGroupByID(): duplicate key exists, id: %d", id)
	}
}

// GetUsersByID gets the users of the given id from the middleware
func (mcr *MySQLClusterRepo) GetUsersByID(id int) ([]metadata.User, error) {
	sql := `
		SELECT DISTINCT ui.id,
						ui.user_name,
						ui.department_name,
						ui.employee_id,
						ui.account_name,
						ui.email,
						ui.telephone,
						ui.mobile,
						ui.role,
						ui.del_flag,
						ui.create_time,
						ui.last_update_time
		FROM t_meta_user_info ui
		    INNER JOIN t_meta_mysql_cluster_user_map mcum ON ui.id = mcum.user_id
			INNER JOIN t_meta_mysql_cluster_info mci ON mcum.mysql_cluster_id = mci.id
		WHERE ui.del_flag = 0
		  AND mcum.del_flag = 0
		  AND mci.del_flag = 0
		  AND mci.id = ?;
	`
	log.Debugf("metadata MySQLClusterRepo.GetUsersByID() sql: \n%s\nplaceholders: %d", sql, id)

	result, err := mcr.Execute(sql, id)
	if err != nil {
		return nil, err
	}

	userList := make([]metadata.User, result.RowNumber())
	for row := range userList {
		userList[row] = NewEmptyUserInfoWithGlobal()
	}
	// map to struct
	err = result.MapToStructSlice(userList, constant.DefaultMiddlewareTag)
	if err != nil {
		return nil, err
	}

	return userList, nil
}

// AddUser add a user for mysql cluster in the middleware
func (mcr *MySQLClusterRepo) AddUser(mysqlClusterID, userID int) error {
	userRepo := NewUserRepoWithGlobal()
	_, err := userRepo.GetByID(userID)
	if err != nil {
		return err
	}
	_, err = mcr.GetByID(mysqlClusterID)
	if err != nil {
		return err
	}
	sql := `INSERT INTO t_meta_mysql_cluster_user_map(mysql_cluster_id, user_id) VALUES(?, ?);`
	log.Debugf("metadata MySQLClusterRepo.AddUser() insert sql: %s", sql)
	_, err = mcr.Execute(sql, mysqlClusterID, userID)

	return err
}

// DeleteUser delete a user for mysql cluster in the middleware
func (mcr *MySQLClusterRepo) DeleteUser(mysqlClusterID, userID int) error {
	userRepo := NewUserRepoWithGlobal()
	_, err := userRepo.GetByID(userID)
	if err != nil {
		return err
	}
	_, err = mcr.GetByID(mysqlClusterID)
	if err != nil {
		return err
	}
	sql := `DELETE FROM t_meta_mysql_cluster_user_map WHERE mysql_cluster_id = ? AND user_id = ?;`
	log.Debugf("metadata AppRepo.DeleteUser() delete sql: %s", sql)
	_, err = mcr.Execute(sql, mysqlClusterID, userID)

	return err
}

// GetAppUsersByID gets the application users of the given id from the middleware
func (mcr *MySQLClusterRepo) GetAppUsersByID(id int) ([]metadata.User, error) {
	sql := `
		SELECT DISTINCT ui.id,
						ui.user_name,
						ui.department_name,
						ui.employee_id,
						ui.account_name,
						ui.email,
						ui.telephone,
						ui.mobile,
						ui.role,
						ui.del_flag,
						ui.create_time,
						ui.last_update_time
		FROM t_meta_user_info ui
			INNER JOIN t_meta_app_user_map aum ON ui.id = aum.user_id
		    INNER JOIN t_meta_app_info ai ON aum.app_id = ai.id
			INNER JOIN t_meta_app_db_map adm ON ai.id = adm.app_id
			INNER JOIN t_meta_db_info di ON adm.db_id = di.id
			INNER JOIN t_meta_mysql_cluster_info mci ON di.cluster_id = mci.id AND di.cluster_type = 1
		WHERE ui.del_flag = 0
		  AND aum.del_flag = 0
		  AND ai.del_flag = 0
		  AND adm.del_flag = 0
		  AND di.del_flag = 0
		  AND mci.del_flag = 0
		  AND mci.id = ? ;
	`
	log.Debugf("metadata MySQLClusterRepo.GetAppUsersByID() sql: \n%s\nplaceholders: %d, %d", sql, id)

	result, err := mcr.Execute(sql, id)
	if err != nil {
		return nil, err
	}

	userList := make([]metadata.User, result.RowNumber())
	for row := range userList {
		userList[row] = NewEmptyUserInfoWithGlobal()
	}
	// map to struct
	err = result.MapToStructSlice(userList, constant.DefaultMiddlewareTag)
	if err != nil {
		return nil, err
	}

	return userList, nil
}

// GetDBUsersByID gets the db users of the given id from the middleware
func (mcr *MySQLClusterRepo) GetDBUsersByID(id int) ([]metadata.User, error) {
	sql := `
		SELECT DISTINCT ui.id,
						ui.user_name,
						ui.department_name,
						ui.employee_id,
						ui.account_name,
						ui.email,
						ui.telephone,
						ui.mobile,
						ui.role,
						ui.del_flag,
						ui.create_time,
						ui.last_update_time
		FROM t_meta_user_info ui
			INNER JOIN t_meta_db_user_map dum ON ui.id = dum.user_id
			INNER JOIN t_meta_db_info di ON dum.db_id = di.id
			INNER JOIN t_meta_mysql_cluster_info mci ON di.cluster_id = mci.id AND di.cluster_type = 1
		WHERE ui.del_flag = 0
		  AND dum.del_flag = 0
		  AND di.del_flag = 0
		  AND mci.del_flag = 0
		  AND mci.id = ? ;
	`
	log.Debugf("metadata MySQLClusterRepo.GetDBUsersByID() sql: \n%s\nplaceholders: %d", sql, id)

	result, err := mcr.Execute(sql, id)
	if err != nil {
		return nil, err
	}

	userList := make([]metadata.User, result.RowNumber())
	for row := range userList {
		userList[row] = NewEmptyUserInfoWithGlobal()
	}
	// map to struct
	err = result.MapToStructSlice(userList, constant.DefaultMiddlewareTag)
	if err != nil {
		return nil, err
	}

	return userList, nil
}

// GetAllUsersByID gets both application and db users of the given id from the middleware
func (mcr *MySQLClusterRepo) GetAllUsersByID(id int) ([]metadata.User, error) {
	sql := `
		SELECT DISTINCT ui.id,
			   			ui.user_name,
			   			ui.department_name,
			   			ui.employee_id,
			   			ui.account_name,
			   			ui.email,
			   			ui.telephone,
			   			ui.mobile,
			   			ui.role,
			   			ui.del_flag,
			   			ui.create_time,
			   			ui.last_update_time
		FROM t_meta_user_info ui
			INNER JOIN t_meta_app_user_map aum ON ui.id = aum.user_id
			INNER JOIN t_meta_app_db_map adm ON aum.app_id = adm.app_id
			INNER JOIN t_meta_db_info di ON di.id = adm.db_id
			INNER JOIN t_meta_mysql_cluster_info mci ON di.cluster_id = mci.id AND di.cluster_type = 1
		WHERE ui.del_flag = 0
		  AND aum.del_flag = 0
		  AND di.del_flag = 0
		  AND adm.del_flag = 0
		  AND mci.del_flag = 0
		  AND mci.id = ?
		UNION
		SELECT DISTINCT ui.id,
			   			ui.user_name,
			   			ui.department_name,
			   			ui.employee_id,
			   			ui.account_name,
			   			ui.email,
			   			ui.telephone,
			   			ui.mobile,
			   			ui.role,
			   			ui.del_flag,
			   			ui.create_time,
			   			ui.last_update_time
		FROM t_meta_user_info ui
			INNER JOIN t_meta_db_user_map dum ON ui.id = dum.user_id
			INNER JOIN t_meta_db_info di ON dum.db_id = di.id
			INNER JOIN t_meta_mysql_cluster_info mci ON di.cluster_id = mci.id AND di.cluster_type = 1
		WHERE ui.del_flag = 0
		  AND dum.del_flag = 0
		  AND di.del_flag = 0
		  AND mci.del_flag = 0
		  AND mci.id = ?
		UNION
		SELECT DISTINCT ui.id,
						ui.user_name,
						ui.department_name,
						ui.employee_id,
						ui.account_name,
						ui.email,
						ui.telephone,
						ui.mobile,
						ui.role,
						ui.del_flag,
						ui.create_time,
						ui.last_update_time
		FROM t_meta_user_info ui
			INNER JOIN t_meta_mysql_cluster_user_map mcum ON ui.id = mcum.user_id
			INNER JOIN t_meta_mysql_cluster_info mci ON mci.id = mcum.mysql_cluster_id
		WHERE ui.del_flag = 0
		  AND mcum.del_flag = 0
		  AND mci.del_flag = 0
		  AND mci.id = ?
		UNION
		SELECT DISTINCT ui.id,
			   			ui.user_name,
			   			ui.department_name,
			   			ui.employee_id,
			   			ui.account_name,
			   			ui.email,
			   			ui.telephone,
			   			ui.mobile,
			   			ui.role,
			   			ui.del_flag,
			   			ui.create_time,
			   			ui.last_update_time
		FROM t_meta_user_info ui
			INNER JOIN t_meta_resource_role_user_map rrum ON ui.id = rrum.user_id
			INNER JOIN t_meta_resource_role_info rri ON rrum.resource_role_id = rri.id
			INNER JOIN t_meta_resource_group_resource_role_map rgrrm ON rri.id = rgrrm.resource_role_id
			INNER JOIN t_meta_resource_group_info rgi ON rgrrm.resource_group_id = rgi.id
			INNER JOIN t_meta_mysql_cluster_resource_group_map mcrgm ON rgi.id = mcrgm.mysql_cluster_id
			INNER JOIN t_meta_mysql_cluster_info mci ON mcrgm.mysql_cluster_id = mci.id
		WHERE ui.del_flag = 0
		  AND rrum.del_flag = 0
		  AND rri.del_flag = 0
		  AND rgi.del_flag = 0
		  AND mcrgm.del_flag = 0
		  AND mci.del_flag = 0
		  AND mci.id = ?;
	`
	log.Debugf("metadata MySQLClusterRepo.GetAppUsersByID() sql: \n%s\nplaceholders: %d, %d, %d, %d", sql, id, id, id, id)

	result, err := mcr.Execute(sql, id, id, id, id)
	if err != nil {
		return nil, err
	}

	userList := make([]metadata.User, result.RowNumber())
	for row := range userList {
		userList[row] = NewEmptyUserInfoWithGlobal()
	}
	// map to struct
	err = result.MapToStructSlice(userList, constant.DefaultMiddlewareTag)
	if err != nil {
		return nil, err
	}

	return userList, nil
}

// Create creates data with given entity in the middleware
func (mcr *MySQLClusterRepo) Create(mysqlCluster metadata.MySQLCluster) (metadata.MySQLCluster, error) {
	sql := `
		INSERT INTO t_meta_mysql_cluster_info(cluster_name,middleware_cluster_id,
			 monitor_system_id, env_id)
		VALUES(?,?,?,?);`
	log.Debugf("metadata MySQLClusterRepo.Create() insert sql: %s", sql)
	// execute
	_, err := mcr.Execute(sql,
		mysqlCluster.GetClusterName(),
		mysqlCluster.GetMiddlewareClusterID(),
		mysqlCluster.GetMonitorSystemID(),
		// mysqlCluster.GetOwnerID(),
		mysqlCluster.GetEnvID())
	if err != nil {
		return nil, err
	}
	// get cluster id
	id, err := mcr.GetID(mysqlCluster.GetClusterName())
	if err != nil {
		return nil, err
	}
	// get entity
	return mcr.GetByID(id)
}

// Update updates data with given entity in the middleware
func (mcr *MySQLClusterRepo) Update(entity metadata.MySQLCluster) error {
	sql := `
		UPDATE t_meta_mysql_cluster_info SET cluster_name = ?, middleware_cluster_id = ?,
			monitor_system_id = ?, env_id = ?, del_flag = ?
		WHERE id = ?;`
	log.Debugf("metadata MySQLClusterRepo.Update() update sql: %s", sql)
	_, err := mcr.Execute(sql,
		entity.GetClusterName(),
		entity.GetMiddlewareClusterID(),
		entity.GetMonitorSystemID(),
		entity.GetEnvID(),
		entity.GetDelFlag(),
		entity.Identity(),
	)

	return err
}

// Delete deletes data in the middleware, it is recommended to use soft deletion,
// therefore use update instead of delete
func (mcr *MySQLClusterRepo) Delete(id int) error {
	tx, err := mcr.Transaction()
	if err != nil {
		return err
	}
	defer func() {
		err = tx.Close()
		if err != nil {
			log.Errorf("metadata ResourceRoleRepo.Delete(): close database connection failed.\n%+v", err)
		}
	}()

	err = tx.Begin()
	if err != nil {
		return err
	}
	sql := `DELETE FROM t_meta_mysql_cluster_info WHERE id = ?;`
	log.Debugf("metadata MySQLClusterRepo.Delete() delete sql(t_meta_mysql_cluster_info): %s", sql)
	_, err = mcr.Execute(sql, id)
	if err != nil {
		return err
	}

	sql = `DELETE FROM t_meta_mysql_cluster_resource_group_map WHERE mysql_cluster_id = ?;`
	log.Debugf("metadata MySQLClusterRepo.Delete() delete sql(t_meta_mysql_cluster_resource_group_map): %s", sql)
	_, err = mcr.Execute(sql, id)
	if err != nil {
		return err
	}

	sql = `DELETE FROM t_meta_mysql_cluster_user_map WHERE mysql_cluster_id = ?;`
	log.Debugf("metadata MySQLClusterRepo.Delete() delete sql(t_meta_mysql_cluster_user_map): %s", sql)
	_, err = mcr.Execute(sql, id)
	if err != nil {
		return err
	}

	return tx.Commit()
}
